﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace Red.Launcher.ViewModels.Workspaces.Database.Sql
{
    public class Transaction
    {
        #region Private Statics

        private static int _nextFreeTransactionId = 0;

        #endregion Private Statics

        #region Private Data

        private int _transactionId;
        private Dictionary<string,SortedSet<Type>> _dependencies = new Dictionary<string, SortedSet<Type>>();
        private Dictionary<string,List<Insert>> _insertQueries = new Dictionary<string, List<Insert>>();
        private Dictionary<string,List<Update>> _updateQueries = new Dictionary<string, List<Update>>();
        private Dictionary<string,List<Delete>> _deleteQueries = new Dictionary<string, List<Delete>>();
        private Dictionary<string,int> _nextInsertIndex = new Dictionary<string,int>();
        private Dictionary<Record,Insert> _insertMap = new Dictionary<Record,Insert>();

        #endregion Private Data

        #region Private Classes

        /// <summary>
        /// The standard C# RTTI Type class does not have a comparison method, so this exists so that I can add more than one Type instance to a <see cref="SortedSet{T}"/>
        /// </summary>
        private class TypeComparer : IComparer<Type>
        {
            public int Compare( Type x, Type y )
            {
                int xHash = x.GetHashCode();
                int yHash = y.GetHashCode();

                if ( xHash < yHash )
                {
                    return -1;
                }
                else if ( xHash > yHash )
                {
                    return 1;
                }

                return 0;
            }
        }

        #endregion Private Classes

        #region Private Methods

        private void AddDependency( string table, Type dependency )
        {
            SortedSet< Type > types;

            if ( !_dependencies.TryGetValue( table, out types ) )
            {
                types = new SortedSet<Type>( new TypeComparer() );
                _dependencies.Add( table, types );
            }

            types.Add( dependency );
        }

        private void RecurseDependencies( Metadata ancestor, Metadata me )
        {
            IEnumerable<Type> allDependencies = me.Children.Concat( me.Joins );
            foreach ( Type type in allDependencies )
            {
                Metadata child = (Metadata)type.GetProperty( "METADATA" ).GetValue( null, null );

                AddDependency( ancestor.Table, type );

                RecurseDependencies( ancestor, child );
            }
        }

        private void RecurseDependencies( Record record )
        {
            Metadata metadata = record.Metadata;
            if ( !_dependencies.ContainsKey( metadata.Table ) )
            {
                RecurseDependencies( metadata, metadata );
            }
        }

        private IEnumerable<SqlParameter> GetParameters<TQuery>( Dictionary<string, List<TQuery>> source ) where TQuery : Query
        {
            return source.SelectMany( queries => queries.Value.SelectMany( query => query.Parameters ) );
        }

        #endregion Private Methods

        #region C-Tor

        public Transaction()
        {
            _transactionId = ++_nextFreeTransactionId;
        }

        #endregion C-Tor

        #region Public Methods

        private static int Add<QueryType>( ref Dictionary<string, List<QueryType>> container, ref QueryType query ) where QueryType : Query
        {
            List<QueryType> queries;
            if ( !container.TryGetValue( query.TransactionId, out queries ) )
            {
                queries = new List<QueryType>();
                container[ query.TransactionId ] = queries;
            }

            int index = queries.Count;
            queries.Add( query );

            return index;
        }

        public void Add( Insert query )
        {
            Add( ref _insertQueries, ref query );

            int insertIndex = -1;
            _nextInsertIndex.TryGetValue( query.Table, out insertIndex );
            _nextInsertIndex[ query.Table ] = insertIndex + 1;
            _insertMap[ query.Record ] = query;

            query.InsertQueries = _insertMap;
            query.InsertIndex = insertIndex;

            foreach ( Record parent in query.Parents )
            {
                RecurseDependencies( parent );
            }
        }

        public void Add( Update query )
        {
            Add( ref _updateQueries, ref query );
        }

        public void Add( Delete query )
        {
            Add( ref _deleteQueries, ref query );

            RecurseDependencies( query.Record );
        }

        #endregion Public Methods

        #region Public Properties

        public bool IsEmpty { get { return ( _insertQueries.Count + _updateQueries.Count + _deleteQueries.Count ) == 0; } }

        public string CreateSql
        {
            get
            {
                // For creation queries, we need entries in the order of ancestors -> descendents
                // This ensures that parent entries are created and given an id before child entries
                var sortedQueries = from entry in _insertQueries orderby ( ( _dependencies.ContainsKey( entry.Value.First().Table ) ) ? _dependencies[ entry.Value.First().Table ].Count : 0 ) descending select entry;

                string sql = string.Empty;

                foreach ( KeyValuePair<string,int> numInsertsForTable in _nextInsertIndex )
                {
                    // Virtual table to hold ids of created entries
                    sql += $"DECLARE @output_{numInsertsForTable.Key} TABLE (id int)\n";
                }

                foreach ( KeyValuePair<string,List<Insert>> list in sortedQueries )
                {
                    List<Insert> entriesToCreate = list.Value;

                    if ( entriesToCreate.Count > 0 )
                    {
                        string table = entriesToCreate[ 0 ].Table;
                        string columns = entriesToCreate[ 0 ].Columns;
                        string entries = string.Join( ", ", entriesToCreate.Select( entry => entry.Values ) );

                        sql += $"INSERT INTO {Settings.DatabaseName}.[dbo].[{table}] ({columns}) OUTPUT inserted.id INTO @output_{table} VALUES {entries}\n";
                    }
                }

                return sql;
            }
        }

        public string DeleteSql
        {
            get
            {
                // We need to delete entities in order of descendants -> ancestors
                // This ensures that we do not attempt to delete an entry that has
                // a database defined dependency still in effect
                var sortedQueries = from entry in _deleteQueries orderby ( ( _dependencies.ContainsKey( entry.Key ) ) ? _dependencies[ entry.Key ].Count : 0 ) ascending select entry;

                string sql = "";

                foreach ( KeyValuePair<string,List<Delete>> list in sortedQueries )
                {
                    string ids = string.Join( ", ", from query in list.Value select $"'{query.Record.Id}'" );
                    sql += $"DELETE FROM [{Settings.DatabaseName}].[dbo].[{list.Key}] WHERE id IN ({ids})\n";
                }

                return sql;
            }
        }

        public string UpdateSql
        {
            get
            {
                string sql = "";

                foreach ( KeyValuePair<string,List<Update>> list in _updateQueries )
                {
                    foreach ( Update query in list.Value )
                    {
                        sql += query.Sql;
                    }
                }

                return sql;
            }
        }

        public string Sql
        {
            get
            {
                string sql = string.Format( "BEGIN TRANSACTION [Trans{0}] \n\nBEGIN TRY\n\n", _transactionId );

                sql += DeleteSql;
                sql += CreateSql;
                sql += UpdateSql;

                sql += string.Format
                (
                    @"  COMMIT TRANSACTION [Trans{0}]
                        END TRY
                        BEGIN CATCH
                            ROLLBACK TRANSACTION [Trans{0}]

                            DECLARE @ErrorMessage NVARCHAR(4000);
                            DECLARE @ErrorSeverity INT;
                            DECLARE @ErrorState INT;
                            
                            SELECT 
                                @ErrorMessage = ERROR_MESSAGE(),
                                @ErrorSeverity = ERROR_SEVERITY(),
                                @ErrorState = ERROR_STATE();
                            
                            -- Use RAISERROR inside the CATCH block to return error
                            -- information about the original error that caused
                            -- execution to jump to the CATCH block.
                            RAISERROR (@ErrorMessage, -- Message text.
                                        @ErrorSeverity, -- Severity.
                                        @ErrorState -- State.
                                        );
                        END CATCH",
                    _transactionId
                );

                return sql;
            }
        }

        public IEnumerable<SqlParameter> InsertParameters => GetParameters( _insertQueries );
        public IEnumerable<SqlParameter> UpdateParameters => GetParameters( _updateQueries );
        public SqlParameter[] Parameters => InsertParameters.Concat( UpdateParameters ).ToArray();

        #endregion Public Properties
    }
}
